Estos escritos son mies de PostgreSQL. Estas mies han sido -con la ayuda de Dios- recopilados por el Ing. Luis Esteban de Dios Núnez -code_c-. El Sistema de Administración de Base de Datos Relacional -RDBMS- PostgreSQL es una aplicación Cliente - Servidor, donde: postgres o postmaster: Aplicación Servidor PostgreSQL. psql: Aplicación Cliente PostgreSQL. IMPORTANTE: El Servidor se debe levantar con el usuario con que fue instalado -en este caso, postgres-. Luego, el cliente /usr/local/pgsql/bin/psql se debe ejecutar en esta misma consola o en otra pero con este mismo usuario. ************************************************************************************************** * Procedimiento de instalación desde los paquetes *.RPM ************************************************************************************************** - Para crear una cuenta de usuario postgres. root# useradd postgres - Creación del cluster de base de datos. Antes de hacer nada, debemos inicializar un area de almacenamiento de base de datos en el HDD. A esto se le llama cluster de base de datos. Un cluster de base de datos es una colección de base de datos accesibles por una simple instancia de un servidor de base de datos en ejecución. Después de la inicialización, un cluster de base de datos contendrá una base de datos llamada template1. Esta plantilla será usada para crear bases de datos. En términos del sistema de ficheros, un cluster de base de datos será un simple directorio dentro del cual todos los datos serán almacenados. Nosotros le llamaremos directorio de datos o área de datos. Para incializar el cluster empleamos el comando initdb. Para ello ejecutar lo siguiente: root# mkdir /var/lib/pgsql/data root# chwon postgres /var/lib/pgsql/data root# su postgres postgres$ initdb -D /var/lib/pgsql/data - Arrancando el servidor de base de datos. Antes de acceder a la base de datos, debemos arrancar el servidor de base de datos. El servidor de base de datos se llama postmaster. El servidor postmaster debe saber dónde encontrar los datos. $postmaster -D /var/lib/pgsql/data para arrancarlo en background, $postmaster -D /var/lib/pgsql/data > logfile 2>&1 & también, $pg_ctl start -l logfile - Parando el servidor. pg_ctl puede parar el servidor, $pg_ctl stop - Creando usuarios de base de datos. Los usuarios de bases de datos son conceptualmente diferentes de los usuarios del sistema operativo. para crear un usuario, CREATEUSER name para eliminarlo, DROPUSER name - Podemos crear grupos. Estos nos permite manejar los privilegios de los usuarios en forma mas sencilla. CREATE GROUP name Para adicionar usuarios a un grupo, ALTER GROUP name ADD USER name1, name2, ... Para eliminarlos, ALTER GROUP name DROP name1, name2, ... - Podemos establecer los privilegios. ************************************************************************************************** * Procedimiento de instalación desde la Distribución del Código Fuente. ************************************************************************************************** Procedimiento para instalar PostgreSQL 8.2.4 en una plataforma SuSE Linux 10.0. (probado con éxito, por code_c). Desde el interior del paquete postgresql 8.2.4 ejecutar los siguientes comandos, en el siguiente orden: 1. ./configure 2. gmake 3. Para que sea desde la cuenta del superusuario-root-: su 4. gmake install 5. Creamos el usuario postgres desde el cual levantaremos el servidor de base de datos PostgreSQL: adduser postgres -si no existe el enlace simbólico adduser, entonces useradd- 6. Creamos la estructura de directorios en la cual instalaremos el servidor de base de datos: mkdir /usr/local/pgsql/data NOTA: Nótece que aquí lo que se está creando es el directorio data. La estructura /usr/local/pgsql ya fue creada en los pasos anteriores. 7. Asignamos los derechos -lectura, escritura y ejecusión- sobre esta estructura al usuario postgres: chown postgres /usr/local/pgsql/data 8. Nos cambiamos a la cuenta del usuario postgres para continuar el resto del trabajo: su - postgres NOTA: Es conveniente crear aquí el subdirectorio de trabajo /home/postgres para este usuario y asignarle derechos de escritura, sino en el paso 10 tendríamos problemas para crear el fichero logfile al ordenar la ejecución en background. 9. Inicializamos el cluster de base de datos PostgreSQL en la estructura de directorios creada: /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data 10. Inicializamos -en background- el servidor de base de datos PostgreSQL en la estructura de directorios creada: Puede emplearse cualquiera de las siguientes maneras, /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 & /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start NOTA: Si no se ejecuta este comando con éxito, verificar que el usuario postgres tenga derechos de escritura y ejecución sobre su directorio de trabajo -/home/postgres-, porque en él se creará el fichero logfile. 11. Creamos una base de datos PostgreSQL llamada test: /usr/local/pgsql/bin/createdb test 12. Ejecutamos el cliente PostgreSQL -psql- para trabajar con la base de datos test: /usr/local/pgsql/bin/psql test ************************************************************************************************** * Algunas carpetas y ficheros importantes de PostgreSQL: ************************************************************************************************** Esta información es el contenido del script de comandos de configuración pg_config, el cual se encuentra en: /usr/bin. Directorio para los ejecutables(binarios) de los usuarios. /usr/bin Directorio para los ficheros cabeceras(*.h) de las interfaces clientes. /usr/include/pgsql Directorio para los ficheros cabeceras(*.h) del servidor. /usr/include/pgsql/server Directorio para las librerías(código objeto). /usr/lib Directorio para los módulos cargables dinámicamente. /usr/lib/postgresql Algunos ficheros ejecutables presentes en /usr/bin: pg_controldata, pg_ctl, pg_dump, pg_dumpall, pg_dumplo, pg_encoding, pg_id, pg_logger, pg_resetxlog, pg_restore, postgres, postmaster. ************************************************************************************************** * Usos del script pg_ctl:(se encuentra en: /usr/bin) ************************************************************************************************** Esta utilidad(script) nos permite arrancar, parar, reiniciar, recargar ficheros de configuración y reportar el estatus del servidor PostgreSQL(postmaster). Usos: pg_ctl start [-w] [-D DATADIR] [-s] [-l FILENAME] [-o \"OPTIONS\"] pg_ctl stop [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE] pg_ctl restart [-w] [-D DATADIR] [-s] [-m SHUTDOWN-MODE] [-o \"OPTIONS\"] pg_ctl reload [-D DATADIR] [-s] pg_ctl status [-D DATADIR] Opciones comunes: -D DATADIR Directorio del área de almacenamiento de la base de datos. -s Imprime solo errores, no los mensajes formales. -w Espera hasta completar la operación. -W No espera hasta completar la operación. --help Muestra esta ayuda, entonces sale. --version Muestra información sobre la versión, entonces sale. (Por defecto espera para shutdown, pero no para arrancar o reiniciar). Si la opción -D se omite, la variable de entorno PGDATA es usada. Opciones para arrancar o reiniciar: -l FILENAME Escribe(o apendea)el log del servidor en FILENAME. El uso de esta opción es altamente recomendada. -o OPTIONS Opciones de línea de comandos para pasar al postmaster (ejecutable del servidor PostgreSQL). -p PATH-TO-POSTMASTER Normalmente no necesario. Opciones para parar o reiniciar: -m SHUTDOWN-MODE Puede ser 'smart', 'fast', or 'immediate' Los modos shutdown son: smart Abandona despuís que los clientes hayan desconectado. fast Abandona directamente, con el propio shutdown. immediate Abandona sin completar shutdown; will lead to recovery on restart /usr/pgsql /usr/postgresql /usr/share/pgsql /var/lib/pgsql /var/lib/pgsql/data Area de almacenamiento de la base de datos. ************************************************************************************************** * Cómo obtener la ayuda de todos los comandos psql internos y los comandos SQL de PostgreSQL: ************************************************************************************************** Para obtener los comandos internos psql ejecutamos: \? Salida: \a toggle between unaligned and aligned output mode \c[onnect] [DBNAME|- [USER]] connect to new database (currently "books") \C [STRING] set table title, or unset if none \cd [DIR] change the current working directory \copy ... perform SQL COPY with data stream to the client host \copyright show PostgreSQL usage and distribution terms \d [NAME] describe table, index, sequence, or view \d{t|i|s|v|S} [PATTERN] (add "+" for more detail) list tables/indexes/sequences/views/system tables \da [PATTERN] list aggregate functions \dd [PATTERN] show comment for object \dD [PATTERN] list domains \df [PATTERN] list functions (add "+" for more detail) \do [NAME] list operators \dl list large objects, same as \lo_list \dp [PATTERN] list table access privileges \dT [PATTERN] list data types (add "+" for more detail) \du [PATTERN] list users \e [FILE] edit the query buffer (or file) with external editor \echo [STRING] write string to standard output \encoding [ENCODING] show or set client encoding \f [STRING] show or set field separator for unaligned query output \g [FILE] send query buffer to server (and results to file or |pipe) \h [NAME] help on syntax of SQL commands, * for all commands \H toggle HTML output mode (currently off) \i FILE execute commands from file \l list all databases \lo_export, \lo_import, \lo_list, \lo_unlink large object operations \o FILE send all query results to file or |pipe \p show the contents of the query buffer \pset NAME [VALUE] set table output option (NAME := {format|border|expanded|fieldsep|null|recordsep| tuples_only|title|tableattr|pager}) \q quit psql \qecho [STRING] write string to query output stream (see \o) \r reset (clear) the query buffer \s [FILE] display history or save it to file \set [NAME [VALUE]] set internal variable, or list all if no parameters \t show only rows (currently off) \T [STRING] set HTML tag attributes, or unset if none \timing toggle timing of commands (currently off) \unset NAME unset (delete) internal variable \w [FILE] write query buffer to file \x toggle expanded output (currently off) \z [PATTERN] list table access privileges (same as \dp) \! [COMMAND] execute command in shell or start interactive shell Y como se puede ver, para obtener los comandos SQL, estando en el prompt de PostgreSQL, ejecutamos: \h Salida: ABORT CREATE OPERATOR CLASS END ALTER AGGREGATE CREATE OPERATOR EXECUTE ALTER CONVERSION CREATE ROLE EXPLAIN ... luego, si queremos obtenerla en un fichero *.txt tecleamos s, e introducimos el log file: /home/comandos.txt Si queremos ver la sintaxis de un comando específico -por ejemplo: copy-, ejecutamos, \h copy En cambio, la documentación completa está en: /usr/local/pgsql/doc/index.html ************************************************************************************************** * Atajos de teclas para el trabajo dentro de PostgreSQL: ************************************************************************************************** SUMARIO DE COMANDOS LESS Commands marked with * may be preceded by a number, N. Notes in parentheses indicate the behavior if N is given. h H Display this help. q :q Q :Q ZZ Exit. --------------------------------------------------------------------------- MOVIMIENTO e ^E j ^N CR * Forward one line (or N lines). y ^Y k ^K ^P * Backward one line (or N lines). f ^F ^V SPACE * Forward one window (or N lines). b ^B ESC-v * Backward one window (or N lines). z * Forward one window (and set window to N). w * Backward one window (and set window to N). ESC-SPACE * Forward one window, but don't stop at end-of-file. d ^D * Forward one half-window (and set half-window to N). u ^U * Backward one half-window (and set half-window to N). ESC-) RightArrow * Left one half screen width (or N positions). ESC-( LeftArrow * Right one half screen width (or N positions). F Forward forever; like "tail -f". r ^R ^L Repaint screen. R Repaint screen, discarding buffered input. --------------------------------------------------- Default "window" is the screen height. Default "half-window" is half of the screen height. --------------------------------------------------------------------------- BUSQUEDA /pattern * Search forward for (N-th) matching line. ?pattern * Search backward for (N-th) matching line. n * Repeat previous search (for N-th occurrence). N * Repeat previous search in reverse direction. ESC-n * Repeat previous search, spanning files. ESC-N * Repeat previous search, reverse dir. & spanning files. ESC-u Undo (toggle) search highlighting. --------------------------------------------------- Search patterns may be modified by one or more of: ^N or ! Search for NON-matching lines. ^E or * Search multiple files (pass thru END OF FILE). ^F or @ Start search at FIRST file (for /) or last file (for ?). ^K Highlight matches, but don't move (KEEP position). ^R Don't use REGULAR EXPRESSIONS. --------------------------------------------------------------------------- SALTOS g < ESC-< * Go to first line in file (or line N). G > ESC-> * Go to last line in file (or line N). p % * Go to beginning of file (or N percent into file). t * Go to the (N-th) next tag. T * Go to the (N-th) previous tag. { ( [ * Find close bracket } ) ]. } ) ] * Find open bracket { ( [. ESC-^F * Find close bracket . ESC-^B * Find open bracket --------------------------------------------------- Each "find close bracket" command goes forward to the close bracket matching the (N-th) open bracket in the top line. Each "find open bracket" command goes backward to the open bracket matching the (N-th) close bracket in the bottom line. m Mark the current position with . ' Go to a previously marked position. '' Go to the previous position. ^X^X Same as '. --------------------------------------------------- A mark is any upper-case or lower-case letter. Certain marks are predefined: ^ means beginning of the file $ means end of the file --------------------------------------------------------------------------- CAMBIOS EN FICHEROS :e [file] Examine a new file. ^X^V Same as :e. :n * Examine the (N-th) next file from the command line. :p * Examine the (N-th) previous file from the command line. :x * Examine the first (or N-th) file from the command line. :d Delete the current file from the command line list. = ^G :f Print current file name. @ Rotate the code recognition method of the current file. V Print version number of "less". --------------------------------------------------------------------------- MISCELANEAS DE COMANDOS - Toggle a command line option [see OPTIONS below]. -- Toggle a command line option, by name. _ Display the setting of a command line option. __ Display the setting of an option, by name. +cmd Execute the less cmd each time a new file is examined. !command Execute the shell command with $SHELL. |Xcommand Pipe file between current pos & mark X to shell command. v Edit the current file with $VISUAL or $EDITOR. V Print version number of "less". --------------------------------------------------------------------------- OPCIONES Most options may be changed either on the command line, or from within less by using the - or -- command. Options may be given in one of two forms: either a single character preceded by a -, or a name preceeded by --. -? ........ --help Display help (from command line). -a ........ --search-skip-screen Forward search skips current screen. -A ........ --mouse-support Use less mouse support (works only in xterm) -b [N] .... --buffers=[N] Number of buffers. -B ........ --auto-buffers Don't automatically allocate buffers for pipes. -c -C .... --clear-screen --CLEAR-SCREEN Repaint by scrolling/clearing. -d ........ --dumb Dumb terminal. -D [xn.n] . --color=xn.n Set screen colors. (MS-DOS only) -e -E .... --quit-at-eof --QUIT-AT-EOF Quit at end of file. -f ........ --force Force open non-regular files. -F ........ --quit-if-one-screen Quit if entire file fits on first screen. -g ........ --hilite-search Highlight only last match for searches. -G ........ --HILITE-SEARCH Don't highlight any matches for searches. -h [N] .... --max-back-scroll=[N] Backward scroll limit. -i ........ --ignore-case Ignore case in searches that do not contain uppercase. -I ........ --IGNORE-CASE Ignore case in all searches. -j [N] .... --jump-target=[N] Screen position of target lines. -J ........ --status-column Display a status column at left edge of screen. -k [file] . --lesskey-file=[file] Use a lesskey file. -K [charset] --charset=[charset] Specify character set. -L ........ --no-lessopen Ignore the LESSOPEN environment variable. -m -M .... --long-prompt --LONG-PROMPT Set prompt style. -n -N .... --line-numbers --LINE-NUMBERS Use line numbers. -o [file] . --log-file=[file] Copy to log file (standard input only). -O [file] . --LOG-FILE=[file] Copy to log file (unconditionally overwrite). -p [pattern] --pattern=[pattern] Start at pattern (from command line). -P [prompt] --prompt=[prompt] Define new prompt. -q -Q .... --quiet --QUIET --silent --SILENT Quiet the terminal bell. -r -R .... --raw-control-chars --RAW-CONTROL-CHARS Output "raw" control characters. -s ........ --squeeze-blank-lines Squeeze multiple blank lines. -S ........ --chop-long-lines Chop long lines. -t [tag] .. --tag=[tag] Find a tag. -T [tagsfile] --tag-file=[tagsfile] Use an alternate tags file. -u -U .... --underline-special --UNDERLINE-SPECIAL Change handling of backspaces. -V ........ --version Display the version number of "less". -w ........ --hilite-unread Highlight first new line after forward-screen. -W ........ --HILITE-UNREAD Highlight first new line after any forward movement. - ........ --mark-wrong-char Display mark character instead of wrong character. -x [N[,...]] --tabs=[N[,...]] Set tab stops. -X ........ --no-init Don't use termcap init/deinit strings. --no-keypad Don't use termcap keypad init/deinit strings. -y [N] .... --max-forw-scroll=[N] Forward scroll limit. -z [N] .... --window=[N] Set size of window. -Z ........ --change-priority Give priority to the SJIS over the UJIS if you use Japanese. -" [c[c]] . --quotes=[c[c]] Set shell quote characters. -~ ........ --tilde Don't display tildes after end of file. -# [N] .... --shift=[N] Horizontal scroll amount (0 = one half screen width) --------------------------------------------------------------------------- EDICION DE LINEAS These keys can be used to edit text being entered on the "command line" at the bottom of the screen. RightArrow ESC-l Move cursor right one character. LeftArrow ESC-h Move cursor left one character. CNTL-RightArrow ESC-RightArrow ESC-w Move cursor right one word. CNTL-LeftArrow ESC-LeftArrow ESC-b Move cursor left one word. HOME ESC-0 Move cursor to start of line. END ESC-$ Move cursor to end of line. BACKSPACE Delete char to left of cursor. DELETE ESC-x Delete char under cursor. CNTL-BACKSPACE ESC-BACKSPACE Delete word to left of cursor. CNTL-DELETE ESC-DELETE ESC-X Delete word under cursor. CNTL-U ESC (MS-DOS only) Delete entire line. UpArrow ESC-k Retrieve previous command line. DownArrow ESC-j Retrieve next command line. TAB Complete filename & cycle. SHIFT-TAB ESC-TAB Complete filename & reverse cycle. CNTL-L Complete filename, list all. ************************************************************************************************** * Trabajando con bases de datos en PostgreSQL. ************************************************************************************************** - Para destruir una base de datos, Desde el shell prompt: DROPDB name Desde el prompt de PostgreSQL:, DROP DATABASE name; - Para crear bases de datos: Desde el shell prompt: createdb name si informa: CREATE DATABASE //La base de datos fue creada con éxito. createdb: command not found //PostgreSQL no está correctamente instalado. psql: could not connect to server: connection refused... //El servidor no está arrancado. ERROR: CREATE DATABASE: permission denied... //Ud. no tiene privilegios para crear bases de datos. Desde el prompt de psql: CREATE DATABASE name; Ahora si quieremos crearla y establecer su propietario, CREATE DATABASE database_name OWNER user_name; - Para borrar bases de datos: Desde el shell prompt: dropdb name si informa: DROP DATABASE //La base de datos fue borrada con íxito. Desde el prompt de psql: DROP DATABASE name; Sólamente su propietario o el administrador pueden hacer esta tarea. - Para mostrar todas las bases de datos existentes: Desde el shell prompt: postgres@jerusalen> psql -l Desde el prompt de PostgreSQL: books# \l -en la versión 8.2.4 será \z ó \d- Ejemplo de una salida, List of databases Name | Owner | Encoding -----------------+----------+----------- books | postgres | SQL_ASCII ejemplo1 | postgres | SQL_ASCII investigaciones | postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII test | postgres | SQL_ASCII (6 rows) - Para conectar con la base de datos books: Para acceder a una base de datos creada Ud. puede hacerlo por varias vías: desde el shell prompt, con el cliente psql, desde el propio prompt del cliente, con la herramienta gráfica pgaccess, o con la ayuda de un programa escrito con algún lenguaje. Desde el shell prompt: postgres@jerusalen> psql books el sistema informa: Welcome to psql 7.3.4, the PostgreSQL interactive terminal. Type... entonces caeremos en el prompt del cliente interactivo psql, books=> o books=# //si somos un superusuario del servidor PostgreSQL. Desde el prompt de psql, con el comando interno \c: \c[onnect] Database_name [user] podemos conectar con otra base de datos. NOTA: En MySQL los usuarios son implicitamente creados con el comando GRANT, pero aquí en PostgreSQL además de usarse el comando GRANT deben crearse explícitamente los usuarios con el comando: CREATE USER name PASSWORD 'password'; después, los privilegios: GRANT select, insert, update, delete ON tabla TO usuario; Ahora psql esta esperando por sus consultas SQL en el espacio de trabajo que el mantiene. Por ejemplo, podría introducir el siguiente comando para ver la versión de nuestro psql: SELECT version(); Para ver la fecha actual podría introducir: SELECT current_date; Para salir del prompt introduzca el siguiente comando: \q ************************************************************************************************** * Nociones del lenguaje SQL en PostgreSQL: ************************************************************************************************** PostgreSQL es un RDBMS(Relational DataBase Management System) lo que significa que es un sistema para administrar datos almacenados en relaciones. La relación es un término matemático para la tabla. Existen otras formas de organizar las bases de datos. Los ficheros y directorios en los sistemas operativos Unix forman un ejemplo de bases de datos jerárquicas. Un desarrollo más moderno es la base de datos orientada a objeto. Cada tabla tiene un grupo de filas, y las filas tienen las mismas columnas. Y cada columna un tipo de dato específico. Las tablas se agrupan en bases de datos, y una colección de bases de datos es administrada por una simple instancia del servidor PostgreSQL y constituye un cluster de datos. - Creando tablas: PostgreSQL soporta los tipos SQL usuales: int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp, y interval, así como otros tipos de utilidad general y un rico grupo de tipos geométrico. PostgreSQL puede ser personalizado con un arbitrario número de tipos de datos definido por el usuario. Ejemplo: CREATE TABLE customers( customerid int unsigned not null auto_increment primary key, name char(30) not null, address char(40) not null, city char(20) not null ); - Borrando tablas: DROP TABLE name; - Poblando una tabla con filas: INSERT INTO customers VALUES(NULL, "Julie Smith", "25 Oak Street", "Airport West"); ************************************************************************************************** * Compendio de comandos SQL de PostgreSQL. ************************************************************************************************** ************************************************************************************************** * PHP y PostgreSQL. ************************************************************************************************** Funciones: pg_connect() y pg_pconnect() pg_query() pg_fetch_row() pg_fetch_assoc() pg_fetch_array() pg_fetch_object() pg_fetch_result() pg_fetch_all() pg_affected_rows() pg_free_result() pg_num_fields() pg_num_rows() pg_close()